package com.controller;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import com.po.*;

public class Poi {
	
	
	public static List<customer> test(String url) throws IOException {
		//System.out.println("test -- start");
		List<customer> customerList = new ArrayList<customer>();
		File excelFile = new File(url);
		FileInputStream fis;
		
		try {
			fis = new FileInputStream(excelFile);
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fis);	
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
			
			for(int numRow=2;numRow<=(xssfSheet.getPhysicalNumberOfRows()-1);numRow++) {
				
				XSSFRow xssfRow = xssfSheet.getRow(numRow);
				if(xssfRow!=null) {
					customer customer = new customer();
					
					XSSFCell id = xssfRow.getCell(0);
					XSSFCell username = xssfRow.getCell(1);
					XSSFCell jobs = xssfRow.getCell(2);
					XSSFCell phone = xssfRow.getCell(3);
						
					customer.setId((int)id.getNumericCellValue());
					customer.setName(username.getStringCellValue());
					customer.setJob(jobs.getStringCellValue());
					customer.setPhone(String.valueOf((int)phone.getNumericCellValue()));
					
					customerList.add(customer);
				}
				
			}
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}finally {
			System.out.println(customerList.toString());
		}
		
		return customerList;
		
	}
	
	@Test
	public void test2() throws IOException {
		
		test("D:\\eclipse2019\\workspace\\.metadata\\.plugins\\org.eclipse.wst.server.core\\tmp0\\wtpwebapps\\chapter17\\upload\\Customer.xlsx");
	}
	
	@Test
	public void test3() throws IOException {
		//System.out.println("test -- start");
		List<customer> customerList = new ArrayList<customer>();
		File excelFile = new File("D:\\\\eclipse2019\\\\workspace\\\\.metadata\\\\.plugins\\\\org.eclipse.wst.server.core\\\\tmp0\\\\wtpwebapps\\\\chapter17\\\\upload\\\\Customer.xlsx");
		FileInputStream fis;
		
		try {
			fis = new FileInputStream(excelFile);
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fis);	
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
			
			for(int numRow=2;numRow<=(xssfSheet.getPhysicalNumberOfRows()-1);numRow++) {
				XSSFRow xssfRow = xssfSheet.getRow(numRow);
				if(xssfRow!=null) {
					customer customer = new customer();
					
					XSSFCell id = xssfRow.getCell(0);
					XSSFCell username = xssfRow.getCell(1);
					XSSFCell jobs = xssfRow.getCell(2);
					XSSFCell phone = xssfRow.getCell(3);
						
					customer.setId((int)id.getNumericCellValue());
					customer.setName(username.getStringCellValue());
					customer.setJob(jobs.getStringCellValue());
					customer.setPhone(String.valueOf((int)phone.getNumericCellValue()));
					
					System.out.println(customer.toString());
					customerList.add(customer);
					
				}
				
			}
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}finally {
			System.out.println(customerList.toString());
		}
		
	}
	
	
}
